using System;
using System.Text;
using System.Reflection;

namespace NBear.Data.Oracle
{
    /// <summary>
    /// Common Factory to create SQL statements for SqlServer and MS Access
    /// </summary>
    public class OracleStatementFactory : IStatementFactory
    {
        #region Private Members

        private const char PARAMETER_TOKEN = ':';

        #endregion

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="T:SqlStatementFactory"/> class.
        /// </summary>
        public OracleStatementFactory()
        {
        }

        #endregion

        #region Public Members

        /// <summary>
        /// Creates the insert statement.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="includeColumns">The include columns.</param>
        /// <returns></returns>
        public string CreateInsertStatement(string tableName, string additionalInsert, params string[] includeColumns)
        {
            if (includeColumns.Length == 0)
            {
                return null;
            }

            string additionalColumns = "";
            string additionalValues = "";

            if (!(string.IsNullOrEmpty(additionalInsert)))
            {
                foreach (string keyValue in additionalInsert.Split(','))
                {
                    additionalColumns += ", " + keyValue.Split('=')[0].Trim();
                    additionalValues += ", " + keyValue.Split('=')[1].Trim();
                }
            }

            string insertStatement = string.Empty;
            StringBuilder columnList = new StringBuilder();
            StringBuilder valueList = new StringBuilder();

            foreach (string item in includeColumns)
            {
                columnList.Append(string.Format("\"{0}\", ", item.Trim('\"')));
                valueList.Append(string.Format(PARAMETER_TOKEN + "{0}, ", item.Trim('\"')));
            }

            insertStatement = string.Format("INSERT INTO \"{0}\" ( {1} {3} ) VALUES ( {2} {4} )",
                tableName.Trim('\"'), columnList.ToString().TrimEnd(new char[] { ' ', ',' }), valueList.ToString().TrimEnd(new char[] { ' ', ',' }), additionalColumns, additionalValues);

            return insertStatement;	
        }

        /// <summary>
        /// Creates the update statement.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="whereStr">The sql STR.</param>
        /// <param name="includeColumns">The include columns.</param>
        /// <returns></returns>
        public string CreateUpdateStatement(string tableName, string whereStr, params string[] includeColumns)
        {
            if (includeColumns.Length == 0)
            {
                return null;
            }

            string updateStatement = string.Empty;
            StringBuilder setList = new StringBuilder();

            foreach (string item in includeColumns)
            {
                setList.Append(string.Format("\"{0}\" = {1}{0}, ", item.Trim('\"'), PARAMETER_TOKEN));
            }

            updateStatement = string.Format("UPDATE \"{0}\" SET {1} {2}",
                tableName.Trim('\"'), setList.ToString().TrimEnd(new char[] { ' ', ',' }), (whereStr != null ? "WHERE " + whereStr : string.Empty));

            return updateStatement;
        }

        /// <summary>
        /// Creates the delete statement.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="whereStr">The sql STR.</param>
        /// <returns></returns>
        public string CreateDeleteStatement(string tableName, string whereStr)
        {
            return string.Format("DELETE FROM \"{0}\" {1}", tableName.Trim('\"'), (whereStr != null ? "WHERE " + whereStr : string.Empty));
        }

        /// <summary>
        /// Creates the select statement.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="whereStr">The sql STR.</param>
        /// <param name="orderByStr">The order by STR.</param>
        /// <param name="includeColumns">The include columns.</param>
        /// <returns></returns>
        public string CreateSelectStatement(string tableName, string whereStr, string orderByStr, params string[] includeColumns)
        {
            if (includeColumns.Length == 0)
            {
                return null;
            }

            string selectStatement = string.Empty;
            StringBuilder columnList = new StringBuilder();

            foreach (string item in includeColumns)
            {
                columnList.Append(string.Format("{0}, ", (item.Trim() == "*" ? "*" : '\"' + item.Trim('\"') + '\"')));
            }

            if (tableName.Contains("#_Begin#"))
            {
                selectStatement = string.Format("SELECT {0} FROM {1} {2} {3}",
                    columnList.ToString().Trim().TrimEnd(','), tableName.Replace("__AliasName", "\"__AliasName\""),
                    (whereStr != null ? "WHERE " + whereStr : string.Empty), (orderByStr != null ? "ORDER BY " + orderByStr : string.Empty));
            }
            else
            {
                selectStatement = string.Format("SELECT {0} FROM \"{1}\" {2} {3}",
                    columnList.ToString().Trim().TrimEnd(','), tableName.Trim('\"'),
                    (whereStr != null ? "WHERE " + whereStr : string.Empty), (orderByStr != null ? "ORDER BY " + orderByStr : string.Empty));
            }

            return selectStatement;
        }

        #endregion
    }
}
